package com.mhs.DAO;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.mhs.DTO.BenhVienDTO;
import com.mhs.DTO.MySQLiteHelper;
import com.mhs.DTO.PlacemarkDTO;
import com.mhs.dataclassification.processK.ProcessK_BenhVienDTO;
import com.mhs.dataclassification.processSize.ProcessSize_BenhVienDTO;
import com.mhs.dataclassification.processTS.ProcessTS_BenhVienDTO;

public class BenhVienDAO {
	private static final String TAG = BenhVienDAO.class.getName();
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	
	public BenhVienDAO(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}
	public List<BenhVienDTO> getAllInfoBenhVienDTO() {
		List<BenhVienDTO> lstBV = new ArrayList<BenhVienDTO>();
		try{
			String sQuery = "select MaBVclient, MaBV, TenBV, DiaChi, DienThoai, Latitude, Longtitude from benhvien;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				lstBV = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					BenhVienDTO bvDTO = new BenhVienDTO();
					bvDTO.setMABV_CLIENT(cursor.getInt(0));
					bvDTO.setMABV(cursor.getInt(1));
					bvDTO.setTENBV(cursor.getString(2));
					bvDTO.setDIACHI(cursor.getString(3));
					bvDTO.setDIENTHOAI(cursor.getString(4));
					bvDTO.setLATITUDE(cursor.getString(5));
					bvDTO.setLONGITUDE(cursor.getString(6));
					lstBV.add(bvDTO);	
					ProcessK_BenhVienDTO.updateK_BenhVienDTO(bvDTO.getMABV_CLIENT(), database, dbHelper);
					ProcessTS_BenhVienDTO.updateTS_BenhVienDTO_Select(bvDTO.getMABV_CLIENT(), database, dbHelper);
					cursor.moveToNext();
				}
				cursor.close();
			}
		}catch(Exception ex){
			lstBV = new ArrayList<BenhVienDTO>();
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return lstBV;
	}
	
	public List<BenhVienDTO> getAllGeoPoint(){
		List<BenhVienDTO> lstBV = new ArrayList<BenhVienDTO>();
		try{
			String sQuery = "select MaBVclient, MaBV, Latitude, Longtitude from benhvien;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0 || cursor == null){
				lstBV = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					BenhVienDTO bvDTO = new BenhVienDTO();
					bvDTO.setMABV_CLIENT(cursor.getInt(0));
					bvDTO.setMABV(cursor.getInt(1));
					bvDTO.setLATITUDE(cursor.getString(2));
					bvDTO.setLONGITUDE(cursor.getString(3));
					lstBV.add(bvDTO);
					cursor.moveToNext();
				}
				cursor.close();
			}
		}catch(Exception ex){
			lstBV = new ArrayList<BenhVienDTO>();
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return lstBV;
	}
	
	public boolean insertPlaceMarkDTOIntoBenhVien(PlacemarkDTO pmDTO) {
		boolean checkInsert = false;
		List<BenhVienDTO> lstBV = getAllGeoPoint();
		database = dbHelper.getWritableDatabase();
		try{
			// calculate size BenhVienDTO
			int sizeRecord = ProcessSize_BenhVienDTO.calculateSize_BenhVienDTO_Insert(pmDTO);
			// calculate TS
			Long TS = ProcessTS_BenhVienDTO.getCurrentTime();
			Log.i(TAG, "TS = " + TS);
			// calculate TTL: don vi tinh: ngay
			int TTL = 7;	
			if(lstBV == null || lstBV.size() == 0){
				// co the chua co benh vien nao trong db -> insert vo
				String insertQuery = "INSERT INTO benhvien " +
						"(MaBVclient, MABV, TENBV, DIACHI, DIENTHOAI, " +
						"Latitude, Longtitude, K, size, TS, TTL) VALUES" +
						"(null, null, '" + pmDTO.getName() + "', '" + pmDTO.getAddress() + "'," +
						" '" + pmDTO.getFoneNumber() + "', '" + pmDTO.getLatitude() + "', '" + pmDTO.getLongitude() + "'," +
						" 0, " + sizeRecord + ", " + TS.toString() + ", " + TTL + ");";
				database.execSQL(insertQuery);
				checkInsert = true;
			}else if(lstBV.size() > 0 && lstBV != null){
				int checkInsertCondition = 0;
				for(int i = 0;i < lstBV.size();i++){
					BenhVienDTO bvDTO = lstBV.get(i);
					String lat = bvDTO.getLATITUDE();
					String lon = bvDTO.getLONGITUDE();
					if(lat.compareTo(pmDTO.getLatitude()) == 0 && lon.compareTo(pmDTO.getLongitude()) == 0){
						// update 
						int K = ProcessK_BenhVienDTO.getK_BenhVienDTO(bvDTO.getMABV_CLIENT(), database, dbHelper);
						String updateQuery = "UPDATE benhvien SET TENBV = '" + pmDTO.getName() + "'," +
								" DIACHI = '" + pmDTO.getAddress() + "', DIENTHOAI = '" + pmDTO.getFoneNumber() + "'," +
								" K = " + K + ", size = " + sizeRecord + ", TS = '" + TS.toString() + "', TTL = " + TTL + " " +
								" WHERE MaBVclient = '" + bvDTO.getMABV_CLIENT() + "' and Latitude = '" + pmDTO.getLatitude() + "' and Longtitude = '" + pmDTO.getLongitude() + "';";
						database.execSQL(updateQuery);
						checkInsert = true;
						checkInsertCondition = 1;
						break;
					}else{
						checkInsertCondition = 0;
					}
				}
				if(checkInsertCondition == 0){
					// insert
					String insertQuery = "INSERT INTO benhvien " +
							"(MaBVclient, MABV, TENBV, DIACHI, DIENTHOAI, " +
							"Latitude, Longtitude, K, size, TS, TTL) VALUES" +
							"(null, null, '" + pmDTO.getName() + "', '" + pmDTO.getAddress() + "'," +
							" '" + pmDTO.getFoneNumber() + "', '" + pmDTO.getLatitude() + "', '" + pmDTO.getLongitude() + "'," +
							" 0, " + sizeRecord + ", " + TS.toString() + ", " + TTL + ");";
					database.execSQL(insertQuery);
					checkInsert = true;
				}
			}
		}catch(SQLException ex){
			ex.printStackTrace();
			checkInsert = false;
		}finally{
			database.close();
		}

		return checkInsert;
	}

	public BenhVienDTO getBenhVienFromMaBVClient(int maBenhVienClient) {
		BenhVienDTO bvDTO = new BenhVienDTO();
		try{
			String sQuery = "select MaBVclient, MaBV, TenBV, DiaChi, DienThoai, Latitude, Longtitude  from benhvien where MaBVclient = " + maBenhVienClient + ";";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				bvDTO = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {			
					bvDTO.setMABV_CLIENT(cursor.getInt(0));
					bvDTO.setMABV(cursor.getInt(1));
					bvDTO.setTENBV(cursor.getString(2));
					bvDTO.setDIACHI(cursor.getString(3));
					bvDTO.setDIENTHOAI(cursor.getString(4));
					bvDTO.setLATITUDE(cursor.getString(5));
					bvDTO.setLONGITUDE(cursor.getString(6));
					ProcessK_BenhVienDTO.updateK_BenhVienDTO(bvDTO.getMABV_CLIENT(), database, dbHelper);
					ProcessTS_BenhVienDTO.updateTS_BenhVienDTO_Select(bvDTO.getMABV_CLIENT(), database, dbHelper);
					break;
				}
				cursor.close();
			}
		}catch(Exception ex){
			bvDTO = new BenhVienDTO();
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return bvDTO;
	}

	public List<BenhVienDTO> getAllBenhVienDTO() {
		List<BenhVienDTO> lstBV = new ArrayList<BenhVienDTO>();
		try{
			String sQuery = "select MaBVclient, MaBV, K, size, TS, TTL from benhvien;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				lstBV = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					BenhVienDTO bvDTO = new BenhVienDTO();
					bvDTO.setMABV_CLIENT(cursor.getInt(0));
					bvDTO.setMABV(cursor.getInt(1));
					bvDTO.setK_BenhVienDTO(cursor.getInt(2));
					bvDTO.setSize_BenhVienDTO(cursor.getInt(3));
					bvDTO.setTS_BenhVienDTO(new Long(cursor.getString(4)));
					bvDTO.setTTL_BenhVienDTO(cursor.getInt(5));
					lstBV.add(bvDTO);
					cursor.moveToNext();
				}
				cursor.close();
			}
		}catch(Exception ex){
			lstBV = new ArrayList<BenhVienDTO>();
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return lstBV;
	}

	public boolean delBenhVienDTO(Integer idBenhVien) {
		boolean resultDelete = true;
		String sQueryDel = "delete from benhvien where MaBVclient = " + idBenhVien + ";";
		database = dbHelper.getWritableDatabase();
		try{
			database.execSQL(sQueryDel);
			resultDelete = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			resultDelete = false;
		}finally{
			database.close();
		}	
		return resultDelete;
	}
}
